﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;
using Oracle.DataAccess.Client;
namespace ddb_BT1
{
    public static class DataProvider
    {
        public static int AffectData(string SQL)
        {
            try
            {
                switch (DDB_Setting.DBManagement)
                {
                    case DatabaseType.Oracle:
                        return AffectData_Oracle(SQL);
                    case DatabaseType.SqlServer:
                        return AffectData_SqlServer(SQL);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
            return -1;
        }
       private static int AffectData_Oracle(string SQL)
        {
            try
            {
                OracleCommand myCMD = new OracleCommand();
                myCMD.Connection = DataProviderConnection.getConnection() as OracleConnection;
                myCMD.CommandType = CommandType.Text;
                myCMD.CommandText = SQL;
                myCMD.CommandTimeout = 180000;//3 phut 
                int CMDResult = myCMD.ExecuteNonQuery();
                return CMDResult;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi thực thi! " + ex.Message);

            }
        }
        private static int AffectData_SqlServer(string SQL)
        {
            try
            {
                SqlCommand myCMD = new SqlCommand();
                myCMD.Connection = DataProviderConnection.getConnection() as SqlConnection;
                myCMD.CommandType = CommandType.Text;
                myCMD.CommandText = SQL;
                myCMD.CommandTimeout = 180000;//3 phut 
                int CMDResult = myCMD.ExecuteNonQuery();
                return CMDResult;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi thực thi! " + ex.Message);

            }
        }
        public static DataSet getDataSet(string SQL)
        {
            try
            {
                switch (DDB_Setting.DBManagement)
                {
                    case DatabaseType.Oracle:
                        return getDataSetOracle(SQL);
                    case DatabaseType.SqlServer:
                        return getDataSetSqlServer(SQL);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
            return null;
        }
        public static DataTable getDataTable(string SQL)
        {
            try
            {
                switch (DDB_Setting.DBManagement)
                {
                    case DatabaseType.Oracle:
                        return getDataSetOracle(SQL).Tables[0];
                    case DatabaseType.SqlServer:
                        return getDataSetSqlServer(SQL).Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);

            }
            return null;
        }
        private static DataSet getDataSetOracle(string SQL)
        {
            try
            {
                DataSet myDS = new DataSet();
                OracleCommand myCMD = new OracleCommand(SQL, DataProviderConnection.getConnection() as OracleConnection);
                OracleDataAdapter myAD = new OracleDataAdapter(myCMD);            // 
                myCMD.CommandType = CommandType.Text;
                myCMD.CommandTimeout = 180000;//3 phut             
                myAD.Fill(myDS);
                return myDS;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi lấy dữ liệu Oracle! " + ex.Message);

            }
        }
        private static DataSet getDataSetSqlServer(string SQL)
        {
            try
            {
                DataSet myDS = new DataSet();
                SqlCommand myCMD = new SqlCommand(SQL, DataProviderConnection.getConnection() as SqlConnection);
                SqlDataAdapter myAD = new SqlDataAdapter(myCMD);
                myAD.MissingSchemaAction = MissingSchemaAction.AddWithKey;// 
                myCMD.CommandType = CommandType.Text;
                myCMD.CommandTimeout = 180000;//3 phut             
                myAD.Fill(myDS);
                return myDS;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi lấy dữ liệu SqlServer! " + ex.Message);

            }
        }
        public static void ExecuteSQLProcedure(SqlCommand cmd)
        {
            try
            {

              cmd.Connection = DataProviderConnection.getConnection() as SqlConnection;
              cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }

        }

    }

}
